package jhavelogger.data;

import java.util.ArrayList;
import java.util.Vector;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import jhavelogger.models.DBLog;

public class JDBCLogDAO implements LogDAO {
    private Connection connection;

    public JDBCLogDAO(Connection connection) {
	if (connection == null) {
	    throw new IllegalArgumentException("Connection cannot be null");
	}
	this.connection = connection;
    }

    /**
     * @param DBLog
     * 
     *            Used for testing the Database, just a simple INSERT statement.
     *            Takes a DBLog as a parameter.
     */
    @Override
    public void insertLog(DBLog log) throws SQLException {
	if (log == null) {
	    throw new IllegalArgumentException("DBLog cannot be null");
	}

	String ipAddress = log.getIp();
	String userName = log.getUsername();
	String algorithmName = log.getAlgorithmName();
	java.sql.Date date = new java.sql.Date(log.getDate().getTime());
	String city = log.getCity();
	String region = log.getRegion();
	String country = log.getCountry();
	String insertCommand = "INSERT INTO logData (ipAddress, userName, algorithmName, date, city, region, country) VALUES (?, ?, ?, ?, ?, ?, ?)";
	PreparedStatement statement = this.connection.prepareStatement(
		insertCommand, Statement.RETURN_GENERATED_KEYS);
	statement.setString(1, ipAddress);
	statement.setString(2, userName);
	statement.setString(3, algorithmName);
	statement.setDate(4, date);
	statement.setString(5, city);
	statement.setString(6, region);
	statement.setString(7, country);
	statement.executeUpdate();
    }

    /**
     * @return Vector<String>
     * 
     * Queries database to get list of all DISTINCT Usernames
     */
    public Vector<String> getUsernames() throws SQLException {
	Vector<String> userList = new Vector<String>();
	String selectUsersCommand = "SELECT DISTINCT userName FROM logData ORDER BY userName";
	Statement statement = this.connection.createStatement();
	ResultSet rs = statement.executeQuery(selectUsersCommand);
	while (rs.next()) {
	    userList.add(rs.getString(1));
	}
	return userList;
    }

    /**
     * @return Vector<String>
     * 
     * Queries database to get list of all DISTINCT Cities
     */
    public Vector<String> getCities() throws SQLException {
	Vector<String> cityList = new Vector<String>();
	String selectCitiesCommand = "SELECT DISTINCT city FROM logData";
	Statement statement = this.connection.createStatement();
	ResultSet rs = statement.executeQuery(selectCitiesCommand);
	while (rs.next()) {
	    cityList.add(rs.getString(1));
	}
	return cityList;
    }

    /**
     * @return Vector<String>
     * 
     * Queries database to get list of all DISTINCT Regions
     */
    public Vector<String> getRegions() throws SQLException {
	Vector<String> regionList = new Vector<String>();
	String selectCitiesCommand = "SELECT DISTINCT region FROM logData";
	Statement statement = this.connection.createStatement();
	ResultSet rs = statement.executeQuery(selectCitiesCommand);
	while (rs.next()) {
	    regionList.add(rs.getString(1));
	}
	return regionList;
    }

    /**
     * @return Vector<String>
     * 
     * Queries database to get list of all DISTINCT Countries
     */
    public Vector<String> getCountries() throws SQLException {
	Vector<String> countryList = new Vector<String>();
	String selectCountriesCommand = "SELECT DISTINCT country FROM logData";
	Statement statement = this.connection.createStatement();
	ResultSet rs = statement.executeQuery(selectCountriesCommand);
	while (rs.next()) {
	    countryList.add(rs.getString(1));
	}
	return countryList;
    }

    /**
     * @author Roman Bykhovoy, Dexter Hoban
     */
    public ArrayList<String[]> getQueryResults(String username, Date startDate,
	    Date endDate, String city, String region, String country)
	    throws SQLException {

	ArrayList<String[]> bigList = new ArrayList<String[]>();
	java.sql.Date sqlStartDate;
	java.sql.Date sqlEndDate;

	if (username == null) {
	    username = "%";
	}

	if (startDate != null) {
	    sqlStartDate = new java.sql.Date(startDate.getTime());
	} else {
	    sqlStartDate = new java.sql.Date(-900, 0, 0);
	}

	if (endDate != null) {
	    sqlEndDate = new java.sql.Date(endDate.getTime());
	} else {
	    sqlEndDate = new java.sql.Date(8099, 0, 0);
	}

	if (city == null) {
	    city = "%";
	}

	if (region == null) {
	    region = "%";
	}

	if (country == null) {
	    country = "%";
	}

	String giantSELECT = "SELECT id, ipAddress, userName, algorithmName, date, city, region, country FROM logData WHERE city LIKE ? AND region LIKE ? AND country LIKE ? AND userName LIKE ? AND date >= ? AND date <= ? ORDER BY date";
	PreparedStatement statement = this.connection
		.prepareStatement(giantSELECT);
	statement.setString(1, city);
	statement.setString(2, region);
	statement.setString(3, country);
	statement.setString(4, username);
	statement.setDate(5, sqlStartDate);
	statement.setDate(6, sqlEndDate);
	ResultSet rs = statement.executeQuery();

	while (rs.next()) {
	    String[] temp = new String[8];
	    temp[0] = rs.getString(1);
	    temp[1] = rs.getString(2);
	    temp[2] = rs.getString(3);
	    temp[3] = rs.getString(4);
	    temp[4] = rs.getDate(5).toString();
	    temp[5] = rs.getString(6);
	    temp[6] = rs.getString(7);
	    temp[7] = rs.getString(8);
	    bigList.add(temp);
	}
	return bigList;
    }

    /*
     * (non-Javadoc)
     * 
     * @see jhavelogger.data.LogDAO#commit()
     */
    @Override
    public void commit() throws SQLException {
	this.connection.commit();
    }
}